package com.ncloud.crm.mapper;


import java.util.List;
import java.util.Map;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;


@Mapper
public interface SaleDashboardServiceMapper {
	//当前销售的客户数
	@Select(value = ("select count(1) as count from (select cust_id from bs_cust where create_user = #{userId} "
			+ "union select cust_id from bs_o_cust where create_user = #{userId}) a "))
	public String getCountCustByUserId(String userId);
	
	//当前销售本月新增的客户数
	@Select(value = ("select count(1) as count from (select cust_id from bs_cust where "
			+ "create_user = #{userId} and date_format(create_time,'%Y%m') = date_format(curdate(),'%Y%m') "
			+ "union select cust_id from bs_o_cust where create_user = #{userId} and date_format(create_time"
			+ ",'%Y%m') = date_format(curdate(),'%Y%m')) a"))
	public String getNewCountByUserId(String userId);
	
	//客户玫瑰图：当前销售客户总的产出金额对比
	@Select(value = ("select b.cust_id,b.industry,sum(a.received_fee) as fee from (select cust_id,received_fee,"
			+ "create_user from (select discnt_id,discnt_inst_id,create_user,received_fee,cust_id from "
			+ "bs_discnt_fee  UNION select c.discnt_id,c.discnt_inst_id,c.create_user,c.received_fee,c.cust_id "
			+ "from bs_o_discnt_fee c,bs_o_order d where c.order_id = d.order_id) a ) a ,(SELECT create_user,"
			+ "cust_id,industry FROM bs_cust UNION SELECT create_user,cust_id,industry FROM bs_o_cust) b where "
			+ "a.cust_id=b.cust_id and b.create_user = #{userId} GROUP BY b.cust_id,b.industry ORDER BY "
			+ "b.industry"))
	public List<Map<Object,Object>> getCustIndustryFee(String userId);
	
	//客户TOP5列表：客户名称、客户所属行业、客户节点数、客户总订单金额
	@Select(value = ("SELECT cust.cust_id,cust.cust_name,cust.industry,node.count,fee.fee FROM (SELECT cust_id,"
			+ "cust_name,industry FROM bs_cust UNION SELECT a.cust_id,a.cust_name,a.industry FROM bs_o_cust a ,"
			+ "bs_o_order b WHERE a.order_id = b.order_id ) cust,( select cust_id,sum(received_fee) as fee FROM "
			+ "(select discnt_id,discnt_inst_id,create_user,received_fee,cust_id from bs_discnt_fee  UNION select"
			+ " c.discnt_id,c.discnt_inst_id,c.create_user,c.received_fee,c.cust_id from bs_o_discnt_fee c,"
			+ "bs_o_order d where c.order_id = d.order_id)a group by cust_id) fee,(SELECT cust_id,COUNT(node_id)"
			+ " AS COUNT FROM (SELECT cust_id,node_id FROM bs_o_node UNION  SELECT cust_id,node_id FROM bs_node ) "
			+ "node GROUP BY cust_id ) node WHERE cust.cust_id = fee.cust_id  AND fee.cust_id = node.cust_id AND"
			+ " FIND_IN_SET(node.cust_id,f_get_custList(#{userId}))  ORDER BY fee DESC LIMIT 5	"))
	public List<Map<Object,Object>> getCustFeeTop5(String userId);
	
	//当前销售订单金额总数
	@Select(value = ("select sum(received_fee) as fee from (select discnt_id,discnt_inst_id,create_user,received_fee "
			+ "from bs_discnt_fee  UNION select c.discnt_id,c.discnt_inst_id,c.create_user,c.received_fee from "
			+ "bs_o_discnt_fee c,bs_o_order d where c.order_id = d.order_id) a where create_user = #{userId}"))
	public String getSumSaleFee(String userId);
	
	//当前销售本月订单金额总数
	@Select(value = ("select sum(received_fee) as fee from (select discnt_id,discnt_inst_id,create_user,"
			+ "received_fee,create_time from bs_discnt_fee  UNION select c.discnt_id,c.discnt_inst_id,"
			+ "c.create_user,c.received_fee,c.create_time from bs_o_discnt_fee c,bs_o_order d where c.order_id "
			+ "= d.order_id) a where create_user = #{userId} and DATE_FORMAT(create_time,'%Y%m') ="
			+ " DATE_FORMAT(CURDATE(),'%Y%m')"))
	public String getSumSaleThisMonth(String userId);
	
	//当前销售上月结算金额总数
	@Select(value = ("select sum(fee) as fee from sys_chnl_realbill where create_user_id = #{0} "
			+ "and cycle_id=#{1}"))
	public String getSumSaleLastMonth(String userId,String dateTime);
	
	//当前销售5条最新的订单信息
	@Select(value = ("SELECT a.order_id,a.fee,b.cust_id,b.cust_name,b.order_state,a.create_time,f_get_prodName"
			+ "(b.prod_id) AS prod_name FROM (SELECT order_id,create_time,SUM(received_fee) AS fee FROM bs_o_discnt_fee"
			+ " GROUP BY order_id,create_time) a,(SELECT order_id,cust_id,cust_name,prod_id,order_state,"
			+ "create_user FROM bs_o_order UNION  SELECT order_id,cust_id,cust_name,prod_id,order_state,"
			+ "create_user FROM bs_oh_order ) b WHERE FIND_IN_SET(b.create_user,f_getUserList(#{0})) and "
			+ "a.order_id=b.order_id ORDER BY create_time DESC LIMIT 5"))
	public List<Map<Object,Object>> getNewOrderTop5(String userId);
	
	
	//当前销售每月数据列表
	@Select(value = ("select month, sale_count from bs_sale_count_monthly where task_id = #{0} and "
			+ "year=#{1} and sale_id=#{2} order by month asc"))
	public List<Map<Object,Object>> getSaleCountByMonth(String taskId,String year,String userId);
		
	
}
